<?php
namespace App\Models;

use DB;
use Illuminate\Database\Eloquent\Model;

class UserM extends Model
{
    static public $STable='lee_user';
    protected $table='lee_user';
    protected $fillable = ['id','name','created_at','updated_at','avatar','nname',
        'gender','address','height','weight','career','birthd',
        'follownum','fansnum','contactinfo','sign','underbustgirth','bra',
        'bust','realunderbustgirth','upperarm','shoulderbreadth','bodylength',
        'armpitgirth','armlength','navelcrotch','navelankle','headgirth',
        'neckcir','waistline','hipline','thighcir','acrosswidth','clanipnav'
        ];
    
    /***
     * 通过 userid 集合返回对应的 用户名
     * @param array $userids
     */
    static  public  function userNames(array $userids){
        return self::whereIn('id',$userids)->
        select(['name'])->get();
    }
    
    
    static  public  function userInfo($userid){
        $userT=self::$STable;
        $sql="select name as username ,sign as sign,nname as nickname,
        address ,height,weight,career,birthd,contactinfo,
        underbustgirth,bra,bust,realunderbustgirth,upperarm,
        shoulderbreadth,bodylength,armpitgirth,armlength,clanipnav,
        navelcrotch,navelankle,neckcir,waistline,hipline,thighcir,acrosswidth,headgirth
        from $userT where id = $userid";
        return DB::select($sql);
    }
    
    /**
     * 更新用户 信息
     * @param array $updateInfo，待更新的用户信息
     * 必包含 用户id标识
     * ['id'=>userid,'avatar'=>imgpath,'nname'=>nickname,
     *   'gender'=>nan,'height'=>181,'weight'=>80,'career'=>'carear1'
     *   'birthd'=>birthd,sign
     *   ]
     */
    static public function updateInfo(array $updateInfo){
         $userid=$updateInfo['id'];
         unset($updateInfo['id']);
        self::updateOrCreate(['id'=>$userid],$updateInfo);
    }
    
    /**
     * 依据用户id返回其 name, Avatar
     * @param bigint $userid 用户id
     */
    static public function getNameAvatar($userid){
        $userInst=self::where(['id'=>$userid])->first();
        if($userInst && $userInst!=null){
            return ['username'=>$userInst->name,'avatar'=>$userInst->avatar];
        }
        return null;
    }
    
    /**
     * 依据用户id集合 返回对应的name，avatar值
     * @param array $userids 用户id集合
     * @return 返回集合 二维数组
     * 如: [
     *      ['userid'=>user1,'username'=>username1,'avatar'=>av1],
     *      ['userid'=>user2,'username'=>username2,'avatar'=>av2]
     *    ] 
     */
    static public function getUserInfo(array $userids){
        $userT=self::$STable;
        $idStr=implode(',', $userids);
        $sql="select id as userid,name as username ,avatar as avatar,sign as sign
            from $userT where id in ( $idStr )";
        return DB::select($sql);
    }
    
    /**
     * 依据用户的id,粉丝数++
     * @param bigint $userid, 用户id
     */
    static public function userFansPlus($userid){
        self::where('id',$userid)->increment('fansnum',1);
    }
    
    /**
     * 依据用户的id,粉丝数--
     * @param bigint $userid, 用户id
     */
    static public function userFansMin($userid){
        self::where('id',$userid)->decrement('fansnum',1);
    }
    
    /**
     * 依据用户的id,关注数++
     * @param bigint $userid, 用户id
     */
    static public function userFollowPlus($userid){
        self::where('id',$userid)->increment('follownum',1);
    }
    
    /**
     * 依据用户的id,粉丝数--
     * @param bigint $userid, 用户id
     */
    static public function userFollowMin($userid){
        self::where('id',$userid)->decrement('follownum',1);
    }
    
    /**
     * 依据 用户id 返回其统计信息(包括 粉丝数 关注数  被点赞数【icon和comment的点赞数】 被收藏数)
     * @param bigint $userid, 用户id
     * 返回： fansnum follownum icnbeliked cmtbeliked icnstorenum
     */
    static public function userStatic($userid){
        $icnT=IconM::$STable;
        $cmtT=CommentM::$STable;
        $userT=self::$STable;
        $iconStoredT=StoreM::$STable;
        
        $sql="select us.fansnum as fansnum,us.follownum as followsnum,
        
            tmp.icnfavournum as icnbelikednum,tmp.icnbestorednum as icnbestorednum,tmp.icnbrowsenum,
            tmp.iconnum as iconnum,
            tmp2.cmtfavournum as cmtbeliked,
            tmp3.storednum as storednum
            from $userT as us
            left outer join
                (select sum(icn.beliked) as icnfavournum,sum(icn.bestored) 
                    as icnbestorednum ,sum(icn.bebrowsed) as icnbrowsenum,
                    count(fid) as iconnum, icn.userid as icnuserid 
                    from $icnT as icn 
                    where icn.userid=$userid group by icn.userid) as tmp
                on tmp.icnuserid =us.id
            left outer join
                (select sum(cmt.beliked) as cmtfavournum ,cmt.userid 
                    as cmtuserid from $cmtT as cmt 
                    where cmt.userid=$userid group by cmt.userid) as tmp2
                on tmp2.cmtuserid =us.id
            left outer join
                (select count(id) as storednum,userid as stuid from $iconStoredT as stt 
                    where stt.userid=$userid  group by stt.userid) as tmp3
                 on tmp3.stuid=us.id
            where us.id=$userid";
        return DB::select($sql);
    }
    
    
    /**
     * 返回用户的详细信息及其 统计信息
     * @param unknown $userid
     */
    static public function DetialStatic($userid){
        $icnT=IconM::$STable;
        $cmtT=CommentM::$STable;
        $userT=self::$STable;
        $iconStoredT=StoreM::$STable;
    
        $sql="select us.fansnum as fansnum,us.follownum as followsnum,
        us.name as username ,us.nname as nickname,
        us.sign as sign,
        us.address as address ,us.height as height,us.weight as weight,us.career as career,
        us.underbustgirth as underbustgirth ,us.bra as bra ,us.bust as bust,us.realunderbustgirth as realunderbustgirth,
        us.upperarm as upperarm,us.shoulderbreadth as  shoulderbreadth,
        us.bodylength as bodylength,us.armpitgirth as armpitgirth,us.armlength as armlength,
        us.navelcrotch as navelcrotch,us.navelankle as navelankle,us.neckcir as neckcir,us.clanipnav as clanipnav,
        us.waistline as waistline,us.hipline as hipline,us.thighcir as thighcir,us.acrosswidth as acrosswidth,
        
        tmp.icnfavournum as icnbelikednum,tmp.icnbestorednum as icnbestorednum,tmp.icnbrowsenum,tmp.iconnum as iconnum,
        tmp2.cmtfavournum as cmtbeliked,
        tmp3.storednum as storednum
        from $userT as us
        left outer join
        (select sum(icn.beliked) as icnfavournum,sum(icn.bestored)
        as icnbestorednum ,sum(icn.bebrowsed) as icnbrowsenum,count(fid) as iconnum,
        icn.userid as icnuserid from $icnT as icn
        where icn.userid=$userid group by icn.userid) as tmp
        on tmp.icnuserid =us.id
        left outer join
        (select sum(cmt.beliked) as cmtfavournum ,cmt.userid
        as cmtuserid from $cmtT as cmt
        where cmt.userid=$userid group by cmt.userid) as tmp2
        on tmp2.cmtuserid =us.id
        left outer join
        (select count(id) as storednum,userid as stuid from $iconStoredT as stt
        where stt.userid=$userid  group by stt.userid) as tmp3
        on tmp3.stuid=us.id
        where us.id=$userid";
        return DB::select($sql);
    }
    
    
    /**
     * 依据用户id  获取用户所发表icon和comment的 被点赞列表 
     * @param bigint $userid 用户id
     */
    static public function belikedList($userid){
        
    }
    
    /**
     * 依据用户id  获取用户所发表icon的被收藏列表
     * @param bigint $userid 用户id
     */
    static public function bestoredList($userid){
        
    }
    
    /**
     * 返回  与指定用户相近的 用户id 
     *  abs(delta(height))*3+ abs(delta(weight))
     * @param unknown $userid
     * @param unknown $lmt
     * @param unknown $off
     */
    static public function getSimilarUsers($userid ,$lmt,$off){
        $t=self::$STable;
        $uf=UserFollowM::$STable;
        $lmt++;
        $sql="select
            b.id as userid ,b.name as username,b.avatar as avatar,
            b.height as height,b.weight as weight,
            b.address as address,b.bra as bra,b.underbustgirth as bust,
            b.fansnum as funsnum
            from 
            $t as b , 
            (select a.height as h ,a.weight as w from $t as a where id=$userid) as c  
            where b.id not in (select uf.followid from $uf as uf  where uf.userid=$userid )
            order by abs(b.height - c.h )*3+abs(b.weight - c.w) asc limit $lmt  offset $off ";
        return DB::select($sql);
    }
    
}

?>
